Retrieve Balance sheet data based on a list of RICs and Data items

Diep.Tran

Hello,

I would appreciate to receive an urgent support regarding retrieving data. I run into an error when trying to retrieve bank balance sheet data based on a set of RICs and Data items. I have documented a list of RICs and Data items and use pandas to read and print it in Python. However, when I get to the ek.get_data code part, there is an error called back.

The code I attempted:

df3, e = ek.get_data(df['RICs'],df1['Dataitem'],parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})

The invalid syntax I received:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

What should I change in this case to make it work?

Thank you so much in advance for any advices and guidances!

PS: The RICs and Dataitems that I created are as below:

jason.ramchandani01

@Diep.Tran The following is definitely working for me:

instruments = ['KAER.VI','TIRO.VI']
data1 = pd.DataFrame()

for i in instruments:
    try:
        df1, e = ek.get_data(i,['TR.F.CashSTDeposDueBanksTot.date','TR.F.CashSTDeposDueBanksTot'],parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})
        if len(data1):
            data1 = data1.append(df1, ignore_index=True)
        else:
            data1 = df1
    except:
        pass
data1

jason.ramchandani01

@Diep.Tran Please try the following - you need to pass in an array of field names, not a dataframe - but thankfully in python this is simple:

df3, e = ek.get_data(df['RICs'].astype(str).values.tolist(),df1['Dataitem'].astype(str).values.tolist(),parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})

df3

Let me know if this works for you.

Diep.Tran

@jason.ramchandani Thank you so much, I am so grateful for your swift comment. I modified the code as you suggested and it works perfectly. However, the dataset that I obtained lacks of the timeseries dimension (as stated in the parameters, I would like to retrieve the data for a period from 31 Dec 1999 to 31 Mar 2021). How can I better improve the code to get the time dimension?

The result that I received is as below: (lacks of time period dimension)

jason.ramchandani01

@Diep.Tran So to add a date field its easy - as you are requesting all company statement examples they should be of the same date - so you could just repeat the first field you have and add .date at the end: TR.F.CashSTDeposDueBanksTot.date

There are many different output types for different fields.You can investigate those in the Data Item Browser App - type DIB into Eikon search bar.

Also I think you will run up against our API limits which is why you are seeing a lot of NAs. You need to be aware of API limits, which for the get_data API are as follows:

• get_data: The current limit value (10-Oct-2019) is around 10,000 data points.

So you will need to break down the request either by chunking the RICs or by requesting years separately (or both) as 505 companies * 89 fields * (20years *4 quarters) is almost 4.5 million datapoints. This is why you are getting NAs. So limit each call to 10K datapoints => 4.5 Mil / 10,000 so that would be 449 iteratve calls. In this case I would just be tempted to try requesting data for each RIC seperately and then append it to a frame as follows:

jason.ramchandani01

@Diep.Tran

instruments = df['RICs'].astype(str).values.tolist()
data1 = pd.DataFrame()

for i in instruments:
    try:
        df1, e = ek.get_data(i,df1['Dataitem'].astype(str).values.tolist(),parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})
        if len(data1):
            data1 = data1.append(df1, ignore_index=True)
        else:
            data1 = df1
    except:
        pass
data1

Diep.Tran

Thank you again for your super detailed guidance. I will modify my command and come back to you if any problems arise.

Diep.Tran

@jason.ramchandani Dear Jason, I have tried the code, but maybe it is the error from my part that the loop only runs for the first ticker and does not return responses for the rest of the RICs code. This is how it looks:

I have an additional problem now that the data item is now lengthened to approx. 400 Data items. I tried to break it down to 4 different parts now and then append them together. However, I would like to ask if it is possible to do it faster in a loop instead of running the code 4 times?

Thank you so much again and sorry for additional concerns.

Diep.Tran

@jason.ramchandani Hello, I tried the code you suggested but it only works for the first ticker in the RIC list and does not seem to append the remaining tickers in the RIC list in the final output?

How should I fix it in this case?

jason.ramchandani01

@Diep.Tran Please can you copy and paste the code you are using. Also please on another line just:

print(df['RICs'].astype(str).values.tolist())

thanks

Diep.Tran

@jason.ramchandani Thank you for your swift reply.

Here is the code I modified

instruments = df['RICs'].astype(str).values.tolist()

print(df['RICs'].astype(str).values.tolist())

data1 = pd.DataFrame()

for i in instruments:

try:

df1, e = ek.get_data(i,df1['Assets'].astype(str).values.tolist(),parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FI0','Frq':'FI','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})

if len(data1):

data1 = data1.append(df1, ignore_index=True)

else:

data1 = df1

except:

pass

data1

jason.ramchandani01

@Diep.Tran I don't think you understood me - I want to see the instrument list so please on a new line print(instruments) or print(df['RICs'].astype(str).values.tolist()) - please remove this line from the main routine. Please post the instrument list here so i can check. thx

Diep.Tran

@jason.ramchandani Sorry for misunderstanding you. Here is the RIC list I have. The only RIC showed up in the final output is the first ticker

Diep.Tran

@jason.ramchandani Sorry for misunderstanding you. Here is the RIC list I have. The only RIC showed up in the final output is the first ticker

Diep.Tran

Thank you for the reply. Can I ask what is inside the data frame df1?

jason.ramchandani01

@Diep.Tran So df1 is the return from the API (ie the data) for each RIC - which is being refreshed on each iteration of the for loop where i is the next RIC in the list of instruments. We append df1 to data1 as we want to build a dataframe for all the RICs.

Diep.Tran

Oh, thank you, then I see where the problem lies it works for me now.